<?php
class ModelShelfGoodsshelf extends Model {
  //list页面数据
  public function getorder($data=array()){
            //$data = $this->trim_all_arr($data);

            $sql = "SELECT
                    s.shelf_id,
                      cd.`name`,
                    c.fullname,
                    c.company_name,
                    s.shelf_name,
                    IF(s.shelf_type = 1,'1500*1200','2200*1200') AS shelf_type,
                    IF(EXISTS(SELECT * FROM shelf_map WHERE shelf_id = s.shelf_id),'已绑定','未绑定') AS qr_code,
                    sum(op.quantity) AS quantity,
                    sum(op.pay_total) AS price,
                    s.date_added,
                    s.date_map,
                    o.date_added AS buytime,
                    o.order_id,
                    u.fullname AS user_id
                    FROM
                      shelf AS s
                    LEFT JOIN category_description AS cd ON s.shelf_category = cd.category_id
                    LEFT JOIN customer AS c ON s.customer_id = c.customer_id
                    LEFT JOIN order_product AS op ON s.shelf_id = op.shelf_id
                    LEFT JOIN `order` AS o ON op.order_id = o.order_id
                    LEFT JOIN user  AS u ON u.user_id = c.user_id
                    WHERE
                      s.`status` != 0
                    AND o.shelf_order_type = 1  AND o.order_status_id!=13
                    ";

            if (!empty($data['filter_recommended_code'])) {
              $sql .= " AND c.recommended_code like '%" . (string)$data['filter_recommended_code'] . "%'";
            }

            if (!empty($data['filter_customer'])) {
              $sql .= " AND c.fullname LIKE '%" . $this->db->escape($data['filter_customer']) . "%'";
            }


            if (!empty($data['filter_date_start'])) {
              $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
            }

            if (!empty($data['filter_date_end'])) {
              $sql .= " AND DATE(s.date_added) >= '" . $this->db->escape($data['filter_date_end']) . "'";
            }


            $sql .= "GROUP BY s.shelf_id order by s.shelf_id desc";


            if (isset($data['start']) || isset($data['limit'])) {
              if ($data['start'] < 0) {
                $data['start'] = 0;
              }

              if ($data['limit'] < 1) {
                $data['limit'] = 20;
              }

              $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
            }
              //var_dump($sql);die;
            $query = $this->db->query($sql);
            return $query->rows;

  }

//list页面分页数据
public function getTotalOrders($data = array()) {
              $sql = "SELECT
                    COUNT(*) AS total
                    FROM
                      shelf AS s
                    LEFT JOIN category_description AS cd ON s.shelf_category = cd.category_id
                    LEFT JOIN customer AS c ON s.customer_id = c.customer_id
                    LEFT JOIN order_product AS op ON s.shelf_id = op.shelf_id
                    LEFT JOIN `order` AS o ON op.order_id = o.order_id
                    WHERE
                      s.`status` != 0
                    AND o.shelf_order_type = 1  AND o.order_status_id!=13
                   ";

     if (!empty($data['filter_recommended_code'])) {
              $sql .= " AND c.recommended_code like '%" . (string)$data['filter_recommended_code'] . "%'";
            }

            if (!empty($data['filter_customer'])) {
              $sql .= " AND c.fullname LIKE '%" . $this->db->escape($data['filter_customer']) . "%'";
            }


            if (!empty($data['filter_date_start'])) {
              $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
            }

            if (!empty($data['filter_date_end'])) {
              $sql .= " AND DATE(s.date_added) >= '" . $this->db->escape($data['filter_date_end']) . "'";
            }

             $sql .=" GROUP BY s.shelf_id";


            $query = $this->db->query($sql);

            return $query->num_rows;
  }


//删除订单记录历史
  public function addshelforderHistory($shelf_order_id){
    return $shelf_order_id;
  }


}
